package org.timeismoney.payment.dao.impl;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import org.hibernate.HibernateException;
import org.hibernate.Session;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.CallableStatementCallback;
import org.springframework.jdbc.core.ColumnMapRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapperResultSetExtractor;
import org.springframework.jdbc.datasource.SingleConnectionDataSource;
import org.springframework.orm.hibernate3.HibernateCallback;
import org.timeismoney.payment.dao.StudentDao;
import org.timeismoney.payment.model.SchoolYear;
import org.timeismoney.payment.model.Student;

public class StudentDaoImpl extends GenericDaoImpl<Student, String> implements
		StudentDao {

	public StudentDaoImpl() {
		this.setClazz(Student.class);
	}

	public void batchInsertStudents(final List<Student> uploadList) {
		getHibernateTemplate().execute(new HibernateCallback() {

			@SuppressWarnings("deprecation")
			public Object doInHibernate(Session session)
					throws HibernateException, SQLException {
				Connection conn = session.connection();
				PreparedStatement ps = conn
						.prepareStatement("insert into student(stu_no,stu_name,stu_pwd,unit_no) "
								+ "values (?,?,?,?)");
				for (Student student : uploadList) {
					ps.setString(1, student.getStuNo());
					ps.setString(2, student.getStuName());
					ps.setString(3, student.getPwd());
					ps.setInt(4, student.getUnit().getUnitNo());
					ps.addBatch();
				}
				ps.executeBatch();
				return null;
			}
		});
	}

	@SuppressWarnings("rawtypes")
	public List getStuPaymentReport(final SchoolYear schoolYear) {
		
		return (List) getHibernateTemplate().execute(new HibernateCallback() {
			
			@SuppressWarnings("deprecation")
			public Object doInHibernate(Session session) throws HibernateException,
					SQLException {
				
				Connection conn = session.connection();
				JdbcTemplate jt = new JdbcTemplate(new SingleConnectionDataSource(conn, false));
				return jt.execute("{call get_payments(?)}", new CallableStatementCallback() {
					
					public Object doInCallableStatement(CallableStatement cs)
							throws SQLException, DataAccessException {
						cs.setInt(1, schoolYear.getSchoolYearNo());
						ResultSet rs = cs.executeQuery();
						RowMapperResultSetExtractor rmrs = new RowMapperResultSetExtractor(new ColumnMapRowMapper());
						return rmrs.extractData(rs);
					}
				});
			}
		});
		
	}
}
